How to Define a Metric Relating Two Tables

In some cases, the data for calculating a metric’s values does not reside in a single table, and you need to join two or more tables to obtain the desired result. In this case, specify one table in the Collect: From Table field and join to the other tables using a sub query in the Collect: Formula field.

You use a subquery in this instance, because the metrics rule composes its own statement with a syntax that includes only one table name (the Collect: From Table) after the "FROM" keyword.

A simplified equivalent of the statement the metric rule forms is as below:

SELECT <collect_group_by>,<collect_formula> FROM <collect_table> WHERE <collect_where_clause> AND <collect date range restriction> GROUP BY <collect_group_by> ORDER BY <collect_group_by>

Example

Field	Value
Metric Title	Replacement Value - Portfolio
Metric Name	eam_PortfolioReplacementValue_monthly
Metric Description	The sum of all Equipment and Buildings replacement value in the portfolio, excluding furniture replacement value.
Business Implication	Variances indicate growth or shrinkage of the portfolio or sections of the portfolio. The metric value can be used to size the total scope or budget of functions, such as building or equipment maintenance, which are a function of replacement value. Used by organizations verifying that the pattern of acquisition and disposal of assets is aligned to their enterprise strategy.
Assumptions	Used by the Enterprise Assets application.
Collect: Recurrence	Every Month, on the 1st day of the month
Collect: From Table	bl
Collect: Formula	SUM(value_market) + (SELECT SUM(eq.cost_replace) FROM eq WHERE eq.date_disposal IS NULL)
Collect: Where Clause	bl_id IN (SELECT ot.bl_id FROM ot WHERE ot.bl_id IS NOT NULL AND ot.date_sold IS NULL)
Report: Trend Direction	On Target is Better
Report Limit Target	450,000,000
Numeric Format	Budget Currency
Metric Granularity Assignments	All

Although the metric formula uses two tables (bl – Buildings and eq - Equipment), the primary table from where the total market value is collected (value_market) is the Buildings (bl) table. A subquery calculates the total equipment replacement cost from the second table (eq). Furthermore, the Collect: Where Clause field joins a third table (ot – Ownership Transactions) in order to restrict the dataset to only those buildings that are still in the organization’s portfolio.

The equivalent SELECT statement would be:

SELECT (SUM(value_market) + (SELECT SUM(eq.cost_replace) FROM eq WHERE eq.date_disposal IS NULL)) as metric_value FROM bl, ot WHERE bl.bl_id = ot.bl_id AND ot.bl_id IS NOT NULL AND ot.date_sold IS NULL